import { Injectable } from '@nestjs/common';
import { U9Repository } from '../../config/U9.module';
import { generator } from '../../common/utils/generator';
import { U9Bom } from './entities/bom.entity';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { BomVo } from './dto/bom.vo';
import { RedisModule } from '../../config/redis.module';
import { Result } from '../../common/result/Result';
import { DictService } from '../06_dict/dict.service';
import { PageResult } from '../../common/result/PageResult';
import { utils, write, writeFile } from 'xlsx';

interface Tree {
  parentId: string; // 父id
  id: string; // 唯一id
  price?: number; // 物料价格
  org: string; // 组织机构
  Code: string; // 物料编码
  SPECS: string; // 型号
  IFA: string; // 制造件|采购件
  itemMaster: string; // 物料id
  RunLaborHours?: number; // 物料用时
  UsageQty?: number; // 用量
  Name?: string; // 物料名称

  SupplierCode?: string; // 供应商编码
  SupplierName?: string; // 供应商名称
  children: Tree[]; // children属性现在是一个Tree类型的数组
}

@Injectable()
export class ForecostService {
  constructor(
    @InjectRepository(U9Bom)
    private u9BomRepository: Repository<U9Bom>,
    private dictService: DictService,
  ) {}

  /**
   * 计算
   * @param org
   * @param code
   * @param itemMaster
   */
  async totalCalculate(org, code, itemMaster, SupplierCode) {
    const cals = await this.calculateService(
      null,
      '1002211230000054',
      code,
      itemMaster,
      SupplierCode,
      false,
      false,
    );
    // 计算总工时、总价格
    let totalPrice = 0;
    let totalRunLaborHours = 0;
    for (const cal of cals) {
      totalPrice += cal.price ? cal.price * cal.UsageQty : 0;
      totalRunLaborHours += cal.RunLaborHours
        ? cal.RunLaborHours * cal.UsageQty
        : 0;
    }
    // 工人时薪、电费费率、采购保险率、制造费率、财务利率、产值估算率
    const config = await this.queryConfigService('bomConfig');
    const result = {};
    result['人工工资'] = (totalRunLaborHours * config.data.salary).toFixed(2);
    result['机械部分总成本'] = (
      totalPrice +
      totalPrice * config.data.PurchasedMaterialPremiumP
    ).toFixed(2);
    result['外购材料'] = totalPrice.toFixed(2);
    result['外购材保费'] = (
      totalPrice * config.data.PurchasedMaterialPremiumP
    ).toFixed(2);
    result['制造费用'] = (
      (config.data.ManufacturingCostP *
        (totalPrice + totalPrice * config.data.PurchasedMaterialPremiumP)) /
      config.data.OutputP
    ).toFixed(2);
    result['电费'] = (
      config.data.electricityCostP *
      ((totalPrice + totalPrice * config.data.PurchasedMaterialPremiumP) /
        config.data.OutputP)
    ).toFixed(2);
    result['财务利息'] = (
      config.data.InterestP *
      ((totalPrice + totalPrice * config.data.PurchasedMaterialPremiumP) /
        config.data.OutputP)
    ).toFixed(2);
    result['合计'] =
      Number(result['人工工资']) +
      Number(result['机械部分总成本']) +
      Number(result['财务利息']) +
      Number(result['制造费用']) +
      Number(result['电费']);
    return result;
  }

  /**
   * 修改BOM信息
   * @param body
   */
  async editBOMService(body: BomVo) {
    const bom = new U9Bom();
    Object.assign(bom, body);
    if (
      await this.u9BomRepository.exists({
        where: { itemMaster: bom.itemMaster },
      })
    ) {
      await this.u9BomRepository.update({ itemMaster: bom.itemMaster }, bom);
    } else {
      await this.u9BomRepository.save(bom);
    }
  }

  /**
   * 1. 获取BOM母表里的所有物料信息，查看是否存在母表里为采购件的情况
   * 2. 存在的问题：检测出没有材料BOM的制造件
   * 3. 检查出
   */
  async getIFAErrs(page) {
    const defaultIFAErrSQL = `
WITH CommonData AS (
    SELECT
        a.id,
        case
            a.Org
            when '1002211230000054' then '天河'
            when '1002211230000293' then '鲁发'
            else ''
        end org,
        a.Code,
        a.Name,
        a.SPECS,
        b.itemMaster,
        case
            a.ItemFormAttribute
            when '4' then '委外加工件'
            when '6' then '虚拟'
            when '9' then '采购件'
            when '10' then '制造件'
            else ''
        end IFA
    FROM
        CBO_ItemMaster a
        LEFT JOIN CBO_BOMMaster b ON a.id = b.itemMaster
    WHERE
        a.ItemFormAttribute IN ('10', '9')
        AND GETDATE() BETWEEN a.Effective_EffectiveDate
            AND a.Effective_DisableDate
)
SELECT org, Code, Name, SPECS, IFA
FROM CommonData
WHERE (itemMaster IS NULL AND IFA = '制造件') OR (itemMaster IS NOT NULL AND IFA = '采购件')
GROUP BY org, Code, Name, SPECS, IFA
ORDER BY org DESC,Code DESC   
OFFSET (${page.pageNum} - 1) * ${page.pageSize} ROWS
FETCH NEXT ${page.pageSize} ROWS ONLY;
`;
    const countSQL = `WITH un as(
    SELECT
        a.id,
        b.itemMaster,
        a.ItemFormAttribute
    FROM
        CBO_ItemMaster a
        LEFT JOIN CBO_BOMMaster b ON a.id = b.itemMaster
    WHERE
        a.ItemFormAttribute IN ('10', '9')
AND GETDATE() BETWEEN a.Effective_EffectiveDate
        AND a.Effective_DisableDate
)
SELECT COUNT(DISTINCT id) as COUNT
FROM un
WHERE (itemMaster IS NOT  NULL AND ItemFormAttribute = '9') OR (itemMaster IS NULL AND ItemFormAttribute = '10');`;
    const list = await U9Repository.query(defaultIFAErrSQL);
    const total = await U9Repository.query(countSQL);
    return PageResult.success(list, total[0].COUNT);
  }

  /**
   * 获取BOM树型结构
   * @param rootId
   * @param org
   * @param code
   * @param itemMaster
   * @param SupplierCode
   * @param cache
   */
  async getBOMTreeService(
    rootId,
    org,
    code,
    itemMaster,
    SupplierCode: string,
    cache: boolean,
  ) {
    if (!itemMaster) {
      itemMaster = await this.getItemMasterByOrgAndCode(org, code);
      if (!itemMaster) {
        return;
      }
    }
    if (!SupplierCode) {
      SupplierCode = "''";
    }
    if (cache) {
      const str = await RedisModule.redisClient.get(itemMaster);
      if (str) {
        return JSON.parse(str);
      }
    }
    const parent = await this.getParent(itemMaster, SupplierCode); // 顶层
    const result = await this.BOMTree('parent', itemMaster, SupplierCode); // 嵌套子层
    result.push(...parent);
    result.forEach((re) => {
      re.CodeName = `${re.Code}(${re.Name})`;
    });
    // todo 根据字典表,修改物料类型为采购件
    const bomItemMasterType =
      await this.dictService.dictOptions('BOMItemMasterType');
    bomItemMasterType.data.forEach((d) => {
      result.forEach((r) => {
        if (d.label === r.Code) {
          r.IFA = d.value;
        }
      });
    });
    const tree = generator.buildTree(result, null);
    if (cache) {
      await RedisModule.redisClient.setex(
        itemMaster,
        3600 * 24,
        JSON.stringify(tree),
      );
    }
    // console.log(JSON.stringify(tree));
    return tree;
  }

  /**
   * 展开BOM并计算用量
   * @param rootId
   * @param org
   * @param code
   * @param itemMaster
   * @param SupplierCode
   * @param merge
   * @param cache
   */
  async calculateService(
    rootId,
    org,
    code,
    itemMaster,
    SupplierCode: string,
    merge: boolean,
    cache: boolean,
  ) {
    const tree = await this.getBOMTreeService(
      rootId,
      org,
      code,
      itemMaster,
      SupplierCode,
      cache,
    );
    const leaves = {};
    // dictService 必然取价
    const bomMustPrice = await this.dictService.dictOptions('BOMMustPrice');
    for (const leaf of tree) {
      await this.calculate(leaf, leaves, 0, bomMustPrice.data);
    }
    const list = [];
    for (const k in leaves) {
      list.push(leaves[k]);
    }
    return this.verification(list, merge);
  }

  /** 全面预算,批量出产品的BOM和价格信息 **/
  async allBudgetService(budget: {
    Org: string;
    domains: [{ Code: string; Number: number }];
  }) {
    const SupplierCode = await this.getSupplierCode('Supplier');
    const workBook = utils.book_new();
    let mergeResult = [];
    for (let i = 0; i < budget.domains.length; i++) {
      console.log('开始执行物料>>>', budget.domains[i].Code);
      // 取出内容
      const cal = await this.calculateService(
        null,
        budget.Org,
        budget.domains[i].Code,
        '',
        SupplierCode,
        true,
        false,
      );
      // 用量 x 预算倍数
      for (let j = 0; j < cal.length; j++) {
        cal[j].TotalUsageQty = cal[j].UsageQty * budget.domains[i].Number;
      }
      // 合并Result
      if (i === 0) {
        mergeResult = cal;
      } else {
        const combined = [...mergeResult, ...cal].reduce((acc, current) => {
          const code = current.Code;
          if (!acc[code]) {
            acc[code] = { ...current };
          } else {
            acc[code].TotalUsageQty += current.TotalUsageQty;
          }
          return acc;
        }, {});
        mergeResult = Object.values(combined);
      }
      const vals = await this.transObj2Sheet(cal);
      // 写入 Sheet 物料编码
      const workSheet = utils.aoa_to_sheet(vals);
      utils.book_append_sheet(workBook, workSheet, budget.domains[i].Code);
    }
    const finalVals = await this.transObj2Sheet(mergeResult);
    const finalSheet = utils.aoa_to_sheet(finalVals);
    utils.book_append_sheet(workBook, finalSheet, 'merge');
    const buffer = write(workBook, { bookType: 'xlsx', type: 'buffer' });
    return buffer;
  }

  async getSupplierCode(dicCode: string) {
    const dicResult = await this.dictService.dictOptions(dicCode);
    let SupplierCode = '';
    if (dicResult.data.length > 0) {
      SupplierCode = dicResult.data.map((item) => `'${item.value}'`).join(',');
    }
    return SupplierCode;
  }

  /**
   * 对象转sheet能理解的数组
   * @param obj
   * @private
   */
  private async transObj2Sheet(obj: any) {
    // console.log(obj);
    const result = [];
    result.push([
      '级别',
      '物料编码',
      '名称',
      '规格型号',
      '料品类型',
      '料品类型描述',
      '供应商',
      '供应商名称',
      '用量',
      '总用量',
      '默认工时',
      '默认价格',
      '工时',
      '用量工时',
      '总用量工时',
      '价格',
      '用量价格',
      '总用量价格',
    ]);
    for (let i = 0; i < obj.length; i++) {
      const line = [];
      line.push(obj[i].ExpandLevel ? obj[i].ExpandLevel : null); // 级别
      line.push(obj[i].Code ? obj[i].Code : null); // 物料编码
      line.push(obj[i].name ? obj[i].name : null); // 名称
      line.push(obj[i].SPECS ? obj[i].SPECS : null); // 规格型号
      line.push(obj[i].IFA ? obj[i].IFA : null); // 料品类型
      line.push(obj[i].IFAMsg ? obj[i].IFAMsg : null); // 料品类型描述(采购件子件不取价)
      line.push(obj[i].SupplierCode ? obj[i].SupplierCode : null); // 供应商编码
      line.push(obj[i].SupplierName ? obj[i].SupplierName : null); // 供应商名称
      line.push(obj[i].UsageQty ? obj[i].UsageQty : null); // 用量
      line.push(obj[i].TotalUsageQty ? obj[i].TotalUsageQty : null); // 总用量
      line.push(
        obj[i].defaultRunLaborHours ? obj[i].defaultRunLaborHours : null,
      ); // 默认工时
      line.push(obj[i].defaultPrice ? obj[i].defaultPrice : null); // 默认价格

      line.push(obj[i].RunLaborHours ? obj[i].RunLaborHours : null); // 工时
      line.push(
        obj[i].RunLaborHours ? obj[i].UsageQty * obj[i].RunLaborHours : null,
      ); // 用量工时
      line.push(
        obj[i].RunLaborHours
          ? obj[i].TotalUsageQty * obj[i].RunLaborHours
          : null,
      ); // 总用量工时
      line.push(obj[i].price ? obj[i].price : null); // 价格
      line.push(obj[i].price ? obj[i].price * obj[i].UsageQty : null); // 用量价格
      line.push(obj[i].price ? obj[i].price * obj[i].TotalUsageQty : null); // 总用量价格
      result.push(line);
    }
    return result;
  }

  private async calculate(tree: Tree, result, level, bomMustPrice) {
    // 初始化当前节点的UsageQty 用量
    result[tree.id] = {
      id: tree.id,
      parentId: tree.parentId,
      UsageQty: tree.UsageQty || 0, // 如果UsageQty不存在，则默认为0
      itemMaster: tree.itemMaster,
      name: tree.Name,
      IFA: tree.IFA,
      Code: tree.Code,
      price: tree.price,
      SPECS: tree.SPECS,
      ExpandLevel: level,
      SupplierCode: tree.SupplierCode,
      SupplierName: tree.SupplierName,
      org: tree.org,
      RunLaborHours: tree.RunLaborHours,
    };
    // todo 判断是否是委外加工件，如果是委外件则采用委外的价格取价
    if (result[tree.id].IFA == '委外加工件') {
      const WeiWaiPrice = await this.getWeiWaiPrice(result[tree.id].itemMaster);
      result[tree.id].price = WeiWaiPrice[0].price;
    }
    // 如果存在子节点，则标记为子节点
    if (tree.children.length > 0) {
      result[tree.id].nodeType = 'trunk'; // 枝干节点标记
    } else {
      result[tree.id].nodeType = 'leaf'; // 叶子节点标记
    }
    // 如果存在父节点，并且父节点的 IFA 是采购件，则更新当前节点的价格为0
    // 结合字典表，如果物料在字典表里，则取价
    // 无需取价采购件 | 取价采购件
    if (
      tree.parentId &&
      result[tree.parentId] &&
      (result[tree.parentId].IFA === '采购件' ||
        result[tree.parentId].IFA === '委外加工件' ||
        result[tree.parentId].IFAMsg === '采购件子件')
    ) {
      // 如果父件是顶层采购件，且父件有价格 || 父件是采购子件 则把该层标记为采购件子件
      if (
        (!result[tree.parentId].IFAMsg && result[tree.parentId].price) ||
        result[tree.parentId].IFAMsg === '采购件子件'
      ) {
        // 需要取价的采购件
        result[tree.id].IFAMsg = '采购件子件';
        const price = result[tree.id].price;
        result[tree.id].defaultPrice = result[tree.id].price; // 保留原始取价
        result[tree.id].defaultRunLaborHours = result[tree.id].RunLaborHours; // 保留原始取工时
        result[tree.id].price = 0;
        result[tree.id].RunLaborHours = 0;
        // 必然取价的物料！
        bomMustPrice.forEach((d) => {
          if (d.value == result[tree.id].Code) {
            result[tree.id].price = price;
          }
        });
      }
    }
    // 如果存在父节点，并且父节点的UsageQty已计算，则更新当前节点的UsageQty
    try {
      if (
        tree.parentId &&
        result[tree.parentId] &&
        'UsageQty' in result[tree.parentId]
      ) {
        result[tree.id].UsageQty *= result[tree.parentId].UsageQty;
      }
    } catch (e) {
      // console.log(tree.parentId);
      // console.log(tree.id);
      // console.log('parent>>>', result[tree.parentId]);
      // console.log('child>>>', result[tree.id]);
    }

    level++;
    // 递归计算子节点，并合并结果
    for (const child of tree.children) {
      if (child) {
        const childRes = this.calculate(child, result, level, bomMustPrice);
        // console.log(childRes);
        Object.assign(result, childRes);
      }
    }

    return result;
  }

  private async verification(values: any[], merge: boolean) {
    // 取价优先级 有效时间内 > 订单价格 > 有效时间外
    const dicBOMPresetPrice =
      await this.dictService.dictOptions('BOMPresetPrice');
    // 取工时优先级 有效时间内 > U9工时 > 工时
    const dicBOMRunLaborHours =
      await this.dictService.dictOptions('BOMRunLaborHours');
    // 1. 计算总用量
    // 使用reduce方法创建一个新对象，其中键是itemMaster，值是UsageQty的总和
    if (merge) {
      const mergedItemsMap: { [key: string]: any } = values.reduce(
        (acc, item) => {
          const itemMaster = item.itemMaster;
          // 如果map中已经有了这个itemMaster，累加UsageQty
          if (acc[itemMaster]) {
            acc[itemMaster].UsageQty += item.UsageQty;
          } else {
            // 否则，创建一个新的对象并设置UsageQty
            acc[itemMaster] = { ...item, UsageQty: item.UsageQty };
          }
          return acc;
        },
        {},
      );
      values = Object.values(mergedItemsMap);
    }
    // 2. 计算总价格、总用时
    // 3. 校验自制件无工时、采购件无价格
    for (let i = 0; i < values.length; i++) {
      values[i]['UsageQty'] = Number(values[i]['UsageQty']?.toFixed(2));
      values[i]['RunLaborHours'] = Number(
        values[i]['RunLaborHours']?.toFixed(2),
      );
      values[i]['price'] = Number(values[i]['price']?.toFixed(2));
      // 更新采购价格
      dicBOMPresetPrice.data.forEach((dic) => {
        if (dic.label == values[i].Code && dic.effectiveEndTime) {
          // 取有效时间内的价格
          if (
            dic.effectiveEndTime > new Date() &&
            dic.effectiveStartTime < new Date()
          ) {
            values[i]['price'] = Number(dic.value)?.toFixed(2);
          }
          // 超出有效时间,且无订单价格
          if (dic.effectiveEndTime < new Date() && !values[i]['price']) {
            values[i]['price'] = Number(dic.value)?.toFixed(2);
          }
        }
      });
      // 更新工时
      dicBOMRunLaborHours.data.forEach((dic) => {
        if (dic.label == values[i].Code && dic.effectiveEndTime) {
          // 取有效时间内的工时
          if (dic.effectiveEndTime > new Date()) {
            values[i]['RunLaborHours'] = Number(dic.value)?.toFixed(2);
          }
          // 超出有效时间,且无订单工时
          if (
            dic.effectiveEndTime < new Date() &&
            !values[i]['RunLaborHours']
          ) {
            values[i]['RunLaborHours'] = Number(dic.value)?.toFixed(2);
          }
        }
      });
      const clde = function (value: any) {
        value['verification'] = true;
        value['remark'] = `通过`;
        if (value.IFA == '制造件' && value.IFAMsg !== '采购件子件') {
          value['price'] = null;
          if (value.RunLaborHours == null || value.RunLaborHours == 0) {
            value['verification'] = false;
            value['remark'] = `无工时`;
          }
        }
        if (value.IFA == '采购件' && value.IFAMsg !== '采购件子件') {
          value['RunLaborHours'] = null;
          if (value.price == null || value.price == 0) {
            value['verification'] = false;
            value['remark'] = `无价格`;
          }
        }
        if (value.IFA == '委外加工件' && value.IFAMsg !== '采购件子件') {
          value['RunLaborHours'] = null;
          if (value.price == null || value.price == 0) {
            value['verification'] = false;
            value['remark'] = `无价格`;
          }
        }
        if (value.IFA == '制造件' && value.nodeType == 'leaf') {
          value['verification'] = false;
          value['remark'] = `缺少材料定额`;
        }
      };
      // 更新材料定额
      clde(values[i]);
    }
    return values;
  }

  private async queryItemMaster(itemMaster: string) {
    return await this.u9BomRepository.findOneBy({ itemMaster: itemMaster });
  }

  private async BOMTree(
    parentId: string,
    itemMaster: string,
    SupplierCode: string,
  ) {
    const BomContents = await this.getChildren(
      parentId,
      itemMaster,
      SupplierCode,
    );
    let result = [...BomContents];
    if (BomContents && BomContents.length > 0) {
      for (const content of BomContents) {
        const contents = await this.BOMTree(
          content.id,
          content.itemMaster,
          SupplierCode,
        );
        result = [...result, ...contents]; // 合并BOMContent到result
      }
    }
    return result;
  }
  private async getChildren(
    parentId: string,
    itemMaster: string,
    SupplierCode: string,
  ): Promise<any[]> {
    const DefaultBOMMasterSQL = `SELECT
    TOP 1 a.id AS id,
    a.itemMaster AS itemMaster,
    a.BOMVersionCode AS BOMVersionCode
FROM
    CBO_BOMMaster a
WHERE
    a.itemMaster = '${itemMaster}'
    AND GETDATE() BETWEEN a.EffEctiveDate
    AND a.DisableDate`;
    const BOMMaster = await U9Repository.query(DefaultBOMMasterSQL);
    if (BOMMaster && BOMMaster.length == 1) {
      const DefaultBOMComponentSql = `
with RunLaborHours as (
    SELECT
        ItemMaster as itemMaster,
        RunLaborHours as RunLaborHours
    FROM
        CA_StdWorkingHours
    WHERE
        ToDate > GETDATE()
),
Price as (
    SELECT
        itemMaster,
        price,
        taxrate,
        Code,
        Name
    FROM
        (
            SELECT
                a.iteminfo_itemid AS "itemMaster",
                a.NetFinallyPriceTC AS "price",
                a.taxrate AS "taxrate",
                ROW_NUMBER () OVER (
                    PARTITION BY a.iteminfo_itemid
                    ORDER BY
                        b.BusinessDate DESC
                ) AS rn,
                b.Supplier_Code as Code,
                c.Name AS Name
            FROM
                PM_POLine a
                LEFT JOIN PM_PurchaseOrder b ON a.PurchaseOrder = b.id
                LEFT JOIN CBO_Supplier_Trl c ON C.ID = B.Supplier_Supplier AND C.SysMLFlag = 'zh-CN'
            WHERE
                a.status > 1
                AND b.Supplier_Code not in (${SupplierCode})
        ) T
    WHERE
        T.rn = 1
),
P as (
    SELECT
        '${parentId}' as parentId,
        NEWID() as id,
        a.itemMaster as itemMaster,
        a.UsageQty as UsageQty,
        case
            b.Org
            when '1002211230000054' then '天河'
            when '1002211230000293' then '鲁发'
            else ''
        end org,
        b.Code,
        b.Name,
        b.SPECS,
        case
            b.ItemFormAttribute
            when '4' then '委外加工件'
            when '6' then '虚拟'
            when '9' then '采购件'
            when '10' then '制造件'
            else ''
        end IFA
    FROM
        CBO_BOMComponent a
        LEFT JOIN CBO_ItemMaster b ON a.itemMaster = b.id
    WHERE
        BOMMaster = '${BOMMaster[0].id}'
)
SELECT
    P.*,
    RunLaborHours.RunLaborHours,
    Price.price,
    Price.Code as SupplierCode, 
    Price.Name AS SupplierName,
    Price.taxrate
FROM
    P
    LEFT JOIN RunLaborHours ON p.itemMaster = RunLaborHours.itemMaster
    LEFT JOIN Price ON P.itemMaster = Price.itemMaster`;
      // console.log(DefaultBOMComponentSql);
      return await U9Repository.query(DefaultBOMComponentSql);
    } else {
      return [];
    }
  }
  private async getParent(itemMaster: string, SupplierCode: string) {
    // 母料的用量是1，母料的工时、母料的采购价格
    const DefaultBOMComponentSql = `
with RunLaborHours as (
    SELECT
        ItemMaster as itemMaster,
        RunLaborHours as RunLaborHours
    FROM
        CA_StdWorkingHours
    WHERE
        ToDate > GETDATE()
),
Price as (
    SELECT
        itemMaster,
        price,
        taxrate,
        Code,
        Name
    FROM
        (
            SELECT
                a.iteminfo_itemid AS "itemMaster",
                a.NetFinallyPriceTC AS "price",
                a.taxrate AS "taxrate",
                ROW_NUMBER () OVER (
                    PARTITION BY a.iteminfo_itemid
                    ORDER BY
                        b.BusinessDate DESC
                ) AS rn,
                b.Supplier_Code as Code,
                c.Name AS Name 
            FROM
                PM_POLine a
                LEFT JOIN PM_PurchaseOrder b ON a.PurchaseOrder = b.id
                LEFT JOIN CBO_Supplier_Trl c ON b.Supplier_Supplier = c.ID AND C.SysMLFlag = 'zh-CN'
            WHERE
                a.status > 1
                AND b.Supplier_Code not in (${SupplierCode})
        ) T
    WHERE
        T.rn = 1
),
P as (
    SELECT
        'null' as parentId,
        'parent' as id,
        a.id as itemMaster,
        1 as UsageQty,
        case
            a.Org
            when '1002211230000054' then '天河'
            when '1002211230000293' then '鲁发'
            else ''
        end org,
        a.Code,
        a.Name,
        a.SPECS,
        case
            a.ItemFormAttribute
            when '4' then '委外加工件'
            when '6' then '虚拟'
            when '9' then '采购件'
            when '10' then '制造件'
            else ''
        end IFA
    FROM
        CBO_ItemMaster a
    WHERE
        id = ${itemMaster}
)
SELECT
    P.*,
    RunLaborHours.RunLaborHours,
    Price.price,
    Price.Code as SupplierCode,
    Price.Name as SupplierName,
    Price.taxrate
FROM
    P
    LEFT JOIN RunLaborHours ON p.itemMaster = RunLaborHours.itemMaster
    LEFT JOIN Price ON P.itemMaster = Price.itemMaster`;
    // console.log(DefaultBOMComponentSql);
    return await U9Repository.query(DefaultBOMComponentSql);
  }
  private async getItemMasterByOrgAndCode(
    org: string,
    code: string,
  ): Promise<string> {
    const DefaultItemMasterSQL = `SELECT id as itemMaster
FROM
    CBO_ItemMaster a
WHERE
    code = '${code}'
    AND org = '${org}'
    AND GETDATE() BETWEEN a.Effective_EffectiveDate
    AND a.Effective_DisableDate;`;
    const BOMMaster = await U9Repository.query(DefaultItemMasterSQL);
    // console.log(1);
    if (BOMMaster && BOMMaster.length == 1) {
      return BOMMaster[0].itemMaster;
    } else {
      return '';
    }
  }

  async getBOMByItemMaster(itemMaster: string) {
    const bom = await this.u9BomRepository.findOneBy({
      itemMaster: itemMaster,
    });
    return bom;
  }

  async getAllBomMaster() {
    const allBomMasterSQL = `SELECT itemMaster FROM CBO_BomMaster`;
    return await U9Repository.query(allBomMasterSQL);
  }

  async queryConfigService(name: string) {
    const data = await this.u9BomRepository.query(
      `SELECT data FROM sys_config WHERE name = '${name}'`,
    );
    if (data) {
      return Result.success(JSON.parse(data[0].data));
    }
  }

  async updateConfigService(body: any) {
    await this.u9BomRepository.query(
      `UPDATE sys_config SET data = '${body.data}' WHERE name = '${body.name}'`,
    );
    return Result.success();
  }

  async rmCache(data: any) {
    if (data.itemMaster) {
      await RedisModule.redisClient.del(data.itemMaster);
    } else {
      const itemMaster = await this.getItemMasterByOrgAndCode(
        data.org,
        data.code,
      );
      if (itemMaster) {
        await RedisModule.redisClient.del(itemMaster);
      }
    }
    return Result.success();
  }

  async addCache(key: string, data: any) {
    // 获取当前时间
    await RedisModule.redisClient.set(
      generator.getNowString() + '-' + key,
      JSON.stringify(data),
    );
    return Result.success();
  }

  /**
   * 根据itemMaster过去委外价格
   * @param itemMaster
   */
  async getWeiWaiPrice(itemMaster: string) {
    const allBomMasterSQL = `SELECT TOP 1 (A.TotalNetMnyTC+A.OwnMaterialMnyTC)/A.RcvQtyTU AS price 
FROM PM_RcvLine A
LEFT JOIN PM_Receivement B ON A.Receivement = B.ID
where IsUpDateWMTrans='1' AND ItemInfo_ItemID = '${itemMaster}'
ORDER BY B.BusinessDate DESC;`;
    return await U9Repository.query(allBomMasterSQL);
  }
}
